package database;
import com.sun.istack.internal.NotNull;
import com.sun.istack.internal.Nullable;
import com.sun.xml.internal.ws.api.model.wsdl.WSDLBoundOperation;

import java.sql.*;
import java.util.*;

/**
 * @author LLAP
 * @version 1.0
 * Created by LLAP on 2017/12/6.
 * Copyright (c) 2017 LLAP. All rights reserved.
 */

public class DBO {
    protected String server = "localhost";
    protected int port = 1433;
    protected String uname;
    protected String pword;
    private String database;
    protected String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private Connection conn;
    private boolean connected = false;

    /**
     * 使用指定参数连接数据库并创建实例
     * @param server 服务器地址，默认为localhost
     * @param port 连接端口，默认为1433
     * @param uname 数据库登录名
     * @param pword 登录密码
     * @param database 要连接的数据库
     */
    public DBO(String server, int port, String uname, String pword, String database) {
        this.server = server;
        this.port = port;
        this.uname = uname;
        this.pword = pword;
        this.database = database;
        this.connect();
    }
    public DBO(String username, String password, String database) {
        this("localhost", 1433, username, password, database);
    }
    public DBO() {
        this("sa", "123456", "Agency");
    }

    @Override
    protected void finalize() throws Throwable {
        super.finalize();
        this.close();
    }

    public DBO connect() {
        if (!connected) {
            String uri = "jdbc:sqlserver://" + server + ":" + port + ";DatabaseName=" + database;
            try {
                Class.forName(driver);
                conn = DriverManager.getConnection(uri, uname, pword);
//                System.out.println("Connected");
                connected = true;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return this;
    }

    /**
     * 关闭单个数据库操作实例的连接
     */
    public void close() {
        if (connected) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取实例中的conn对象，便于执行批量等复杂操作。
     * @return Connection conn
     */
    public Connection getConn() {
        return conn;
    }

    /**
     * 预编译SQL语句并返回，便于类外执行复杂操作
     * @param sql 要处理的SQL语句
     * @return 一个由参数sql初始化的PreparedStatement对象
     * @throws SQLException SQL语句有误
     */
    public PreparedStatement prepare(String sql) throws SQLException {
        return conn.prepareStatement(sql);
    }

    /**
     * 在指定表中删除数据
     * @param condition 约束条件，不能为空<br>
     *                  由N个元素组成的Object数组，首元素必须为String<br>
     *                  第一个元素为WHERE的限定条件（变参用?代替），之后的N-1个元素为第1~N-1个变参的传入参数<br>
     *                  例（DBO对象名为db）：若要在表Table中查找所有id为1且name为Name的结果，只需执行<br>
     *                  <pre>
     *                  ResultSet result = db.findAll(
     *                      new Object[] {
     *                              "id = ? and name = ?",
     *                              1,
     *                              "Name"
     *                      }, "Table"
     *                  );
     *                  </pre>
     * @param tableName 要操作的表名
     * @return 删除成功的记录数量
     * @throws SQLException 执行失败
     * @throws IllegalArgumentException 约束条件参数有误
     */
    public int delete(@NotNull Object[] condition, String tableName) throws SQLException, IllegalArgumentException {
        // DELECT FROM ${tableName} [where ${condition}]
        String sql = "DELETE FROM " + tableName + " WHERE ";
        if (condition[0] instanceof String) {
            sql += condition[0];
        }
        else throw new IllegalArgumentException("First element of condition[] must be String!");
        sql += ";";
        PreparedStatement statement;

        statement = conn.prepareStatement(sql);
        for (int i = 1; i < condition.length; i++) {
            if (condition[i] == null) throw new IllegalArgumentException("");
            statement.setString(i, condition[i] + "");
        }
        return statement.executeUpdate();
    }

    public int delete (@NotNull String columnName, @NotNull Object[] keys, String tableName) throws SQLException {
        String sql = "DELETE FROM " + tableName + " WHERE " + columnName + "= ?;";
        PreparedStatement statement = this.prepare(sql);
        int ret = 0;
        for (int i = 0; i < keys.length; i++) {
            statement.setString(1, keys[i].toString());
            System.out.println(keys[i].toString());
            statement.addBatch();
        }
        int[] result = statement.executeBatch();
        for (int i : result) {
            ret += i;
        }
        return ret;
    }

    /**
     * 在指定表中新建一条记录
     * 例：
     * <pre>
     * boolean ret = db.create(
     *                  "Table",
     *                  new HashMap&lt;String, Object&gt;() {{
     *                      put("id", 1);
     *                  }});
     * </pre>
     * 即为在表Table中插入一条字段id为1的记录
     * @param tableName 要操作的表名
     * @param kvSet 指定记录的字段-值映射，接受所有实现Map&lt;K, V&gt;接口的对象
     * @return 返回自增id
     * @throws SQLException 新建时出现异常
     */
    public int create(String tableName, @NotNull Map<String, ?> kvSet) throws SQLException {
        LinkedList<Object> args = new LinkedList<>();
        String sql = "INSERT INTO " + tableName + " (";
        String values = " VALUES (";
        for (Map.Entry<String, ?> entry : kvSet.entrySet()) {
            sql += entry.getKey() + ",";
            args.add(entry.getValue());
            values += "?,";
        }
        sql = sql.substring(0, sql.length() - 1) + ")" +
                values.substring(0, values.length() - 1) + ");";
        PreparedStatement statement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        int i = 1;
        System.out.println(args);
        for (Object v : args) {
            if (v != null) statement.setString(i++, v.toString());
            else statement.setNull(i++, Types.NULL);
        }
        statement.executeUpdate();
        ResultSet rs = statement.getGeneratedKeys();
        rs.next();
        return rs.getInt(1);
    }

    /**
     * 在指定表中批量添加数据
     * @param tableName 要添加的表名
     * @param valueSet 要添加的数据，要求数组中每一个元素均为Map&lt;String, Object&gt;字段-值映射，且每个元素的键值对顺序必须一致
     * @return 添加成功的记录条数
     * @throws SQLException 新建时出现异常
     */
    public int create(String tableName, @NotNull Object[] valueSet) throws SQLException{
//        System.out.println(valueSet.length);
//        System.out.println(valueSet[0].getClass());
        if (valueSet.length <= 0) return 0;
        int ret = 0, i = 1;
        LinkedList<Object> args = new LinkedList<>();
        String sql = "INSERT INTO " + tableName + " (";
        String values = " VALUES (";
        for (Map.Entry<String, ?> entry : ((HashMap<String, Object>)valueSet[0]).entrySet()) {
            sql += entry.getKey() + ",";
//            args.add(entry.getValue());
            values += "?,";
        }
        sql = sql.substring(0, sql.length() - 1) + ")" +
                values.substring(0, values.length() - 1) + ");";
        System.out.println(sql);
        PreparedStatement statement = conn.prepareStatement(sql);

        for (Object q : valueSet) {
            HashMap<String, Object> kvSet = (HashMap<String, Object>)q;
            i = 1;
            args.clear();
            for (Map.Entry<String, ?> entry : kvSet.entrySet()) {
                if (entry.getValue() != null) statement.setString(i++, entry.getValue().toString());
                else statement.setNull(i++, Types.NULL);
            }
            statement.addBatch();
        }
        int result[] = statement.executeBatch();
        for (int q : result) {
            ret += q;
        }
        return ret;
    }

    /**
     *
     * @param condition 约束条件
     * @param tableName 要操作的表名
     * @param kvSet 要更新记录的字段-值映射，接受所有实现Map&lt;K, V&gt;接口的对象
     * @return 更新成功的记录数量
     * @throws SQLException 更新出现异常
     * @throws IllegalArgumentException condition[]数组首元素不为String
     */
    public int update(@NotNull Object[] condition, @NotNull String tableName, @NotNull Map<String, ?> kvSet)
        throws SQLException, IllegalArgumentException {
        LinkedList<Object> args = new LinkedList<>();
        String sql = "UPDATE " + tableName + " SET ";
        String restraint = " WHERE ";

        for (Map.Entry<String, ?> entry : kvSet.entrySet()) {
            sql += entry.getKey() + "=?,";
            args.add(entry.getValue());
        }
        if (condition[0] instanceof String) {
            restraint += condition[0];
            for (int i = 1; i < condition.length; i++) {
                args.add(condition[i]);
            }
        }
        else {
            throw new IllegalArgumentException("First element of condition[] must be String");
        }
        sql = sql.substring(0, sql.length() - 1) + restraint + ";";
        PreparedStatement statement = conn.prepareStatement(sql);
        int i = 1;
        for (Object v : args) {
            if (v != null) statement.setString(i++, v.toString());
            else statement.setNull(i++, Types.NULL);
        }
        return statement.executeUpdate();
    }

    /**
     * 以绑定参数的形式执行一条SQL查询
     * @param sql 要执行的SQL语句
     * @param args 要绑定的参数（按语句中的顺序）
     * @return 查询到的结果集合
     * @throws SQLException 执行失败
     */
    public ResultSet query(String sql, Object[] args) throws SQLException {
//        System.out.println(sql);
        PreparedStatement statement = conn.prepareStatement(sql);
        if (args != null) {
            for (int i = 0; i < args.length; i++) {
                statement.setString(i + 1, args[i] + "");
//                System.out.println(args[i]);
            }
        }
        return statement.executeQuery();
    }
    public ResultSet query(String sql) throws SQLException {
        return this.query(sql, null);
    }

    /**
     * 以绑定参数的方式执行一条SQL操作
     * @param sql 要执行的SQL语句
     * @param args 要绑定的参数（按语句中的顺序）
     * @return 操作成功的记录数量
     * @throws SQLException 执行失败
     */
    public int execute(String sql, Object[] args) throws SQLException {
        PreparedStatement statement = conn.prepareStatement(sql);
        if (args != null) {
            for (int i = 0; i < args.length; i++) {
                statement.setString(i + 1, args[i] + "");
            }
        }
        return statement.executeUpdate();
    }
    public int execute(String sql) throws SQLException {
        return this.execute(sql, null);
    }

    /**
     * 在指定表中查询一条记录
     * @param condition 查询约束条件，可为null
     * @param tableName 要查询的表名
     * @param columnSet 要获取的字段名，默认为*
     * @param orderBy 排序条件，默认为空
     * @return 查询到的结果集合
     * @throws SQLException 查询失败
     */
    public ResultSet find(Object[] condition, String tableName, String columnSet, String orderBy) throws SQLException {
        return this.findAll(condition, tableName, columnSet, orderBy, 1);
    }
    public ResultSet find(Object[] condition, String tableName, String columnSet) throws SQLException {
        return this.find(condition, tableName, columnSet, "");
    }
    public ResultSet find(Object[] condition, String tableName) throws SQLException {
        return this.find(condition, tableName, "*");
    }

    /**
     * 在指定表中查找指定数量的记录
     * findAll有以下几种参数组合：
     * 条件，表名，列名，排序，数量
     * 条件，表名，列名，排序
     * 条件，表名，排序，数量
     * 条件，表名，排序
     * 条件，表名，数量
     * 条件，表名
     * 表名，数量
     * 表名
     * @param condition 查询约束条件，可以为null
     * @param tableName 要查询的表名
     * @param columnSet 要获取的字段，默认为*
     * @param orderBy 排序条件，默认为空
     * @param limit 数量限制，默认为0（不限制）
     * @return 查询到的结果集合
     * @throws SQLException 查询失败
     */
    public ResultSet findAll(Object[] condition, String tableName, String columnSet, String orderBy, int limit) throws SQLException {
        String[] sqlArgs = {"SELECT ", " FROM ", " WHERE ", " ORDER BY ", " TOP "};
        String sql = sqlArgs[0];
        if (limit > 0) {
            sql += sqlArgs[4];
            sql += limit;
            sql += " ";
        }
        sql += columnSet;
        sql += sqlArgs[1];
        sql += tableName;
        if (condition != null) {
            if (condition[0] instanceof String) {
                sql += sqlArgs[2];
                sql += condition[0];
            }
        }
        if (orderBy != null && !orderBy.equals("")) {
            sql += sqlArgs[3];
            sql += orderBy;
        }

        sql += ";";
        //System.out.println(sql);
        PreparedStatement statement = conn.prepareStatement(sql);
        if (condition != null) {
            for (int i = 1; i < condition.length; i++) {
                statement.setString(i, condition[i] + "");
            }
        }
        ResultSet ret = statement.executeQuery();
        return ret;
    }
    public ResultSet findAll(Object[] condition, String tableName, String columnSet, String orderBy) throws SQLException {
        return this.findAll(condition, tableName, columnSet, orderBy, 0);
    }
    public ResultSet findAll(Object[] condition, String tableName, String orderBy, int limit) throws SQLException {
        return this.findAll(condition, tableName, "*", orderBy, limit);
    }
    public ResultSet findAll(Object[] condition, String tableName, String orderBy) throws SQLException {
        return this.findAll(condition, tableName, orderBy, 0);
    }
    public ResultSet findAll(Object[] condition, String tableName, int limit) throws SQLException {
        return this.findAll(condition, tableName, "*", "", limit);
    }
    public ResultSet findAll(Object[] condition, String tableName) throws SQLException {
        return this.findAll(condition, tableName, "*", "");
    }
    public ResultSet findAll(String tableName, int limit) throws SQLException {
        return this.findAll(null, tableName, limit);
    }
    public ResultSet findAll(String tableName) throws SQLException {
        return this.findAll(tableName, 0);
    }

    public static void main(String[] args) {
        DBO db = new DBO("sa", "123456", "Agency");
        ResultSet res;
        try {
            res = db.findAll(
                    null, "test", "id desc"
            );
            /*res = db.query(
                    "select * from test where id = ?;", new Object[] {2});*/
            if (res != null) {
                while(res.next()) {
                    System.out.println(res.getString(1));
                }
            }
            /*int cnt = db.delete(
                    new Object[] {
                            "id = ?",
                            1
                    }, "test"
            );
            System.out.println(cnt);*/
            ArrayList<HashMap<String, Object>> testlist = new ArrayList<>();
            testlist.add(new HashMap<String, Object>() {{
                put("id", 11);
            }});
            testlist.add(new HashMap<String, Object>() {{
                put("id", 12);
            }});
            testlist.add(new HashMap<String, Object>() {{
                put("id", 13);
            }});
            testlist.add(new HashMap<String, Object>() {{
                put("id", 14);
            }});
            System.out.println("***************");
            int ret = db.create(
                    "test",
                    new Object[] {
                            new HashMap<String, Object>() {{
                                put("id", 15);
                            }},
                            new HashMap<String, Object>() {{
                                put("id", 16);
                            }}
                    }
            );

            System.out.println(ret);

            System.out.println("***************");

            db.update(
                    new Object[] {
                            "id = ?",
                            1,
                    },
                    "test",
                    new HashMap<String, Object>() {{
                        put("id", 10);
                    }}
            );
        }
        catch (Exception e) {
            e.printStackTrace();
        }


    }
}
